package LawsonTransformJava.pgm;
import java.sql.*;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.xref.CrossRef;
import LawsonTransformJava.dao.DAOFactoryDB;
import LawsonTransformJava.dao.DAOPayMastr;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblPayMastr;
import LawsonTransformJava.main.ClassMain;

public class PR560RecType3 extends DAObject implements  
	DAOPayMastr, Runnable {

	private TblPayMastr pym = new TblPayMastr();

	private int checkId = 0;
	private String schema = null;
	private PRHistReport errReport = null;

	public PR560RecType3 (File output, FileWriter errOutput, PRHistReport errReport, boolean allowBlanks) throws SQLException {
		// initialization 
		try {
			//Append data to FileWriter = true
			this.output = new FileWriter(this.outputFile = output, true);
			this.errReport = errReport;
			this.errOutput = errOutput;

			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

    public PR560RecType3 (FileWriter outputFile, FileWriter errOutput, PRHistReport errReport, boolean allowBlanks) throws SQLException {
    // initialization 
		try {
			this.output = outputFile;
			this.errReport = errReport;
			this.errOutput = errOutput;
			
			//initialize
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
    }

  private void init(boolean allowBlanks) throws IOException, SQLException {
	    // Set the data variables
		data = DAOFactoryDB.getDAODB(true);
		schema = data.getSchema();
	    
	    stmtData = data.getDataStatement();	    
	    countStmtData = data.getCountStatement();
  }
  
  public void setCheckId(int checkId) {
	  this.checkId=checkId;
	  this.whereClause=null;
  }
  
  public TblPayMastr findPayMastr() {
		try {
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE " + TABLE_NAME + ".CHECK_ID=" + checkId;
			} else {
				whereClause = whereClause.trim() + " AND " + TABLE_NAME + ".CHECK_ID=" + checkId;
			}
//			sqlData = "SELECT * FROM " + data.getSchema() + TABLE_NAME + " " + (whereClause==null?"":whereClause);
			sqlData = "SELECT DISTINCT " + schema + TABLE_NAME + ".CHECK_ID, " +
						schema + TABLE_NAME + ".CHECK_DATE, " +
						schema + TABLE_NAME + ".CHECK_NBR, " +
						schema + TABLE_NAME + ".CHECK_NET, " +
						schema + TABLE_NAME + ".EMPLOYEE, " +
						schema + TABLE_NAME + ".PROCESS_LEVEL, " +
						schema + TABLE_NAME + ".DEPARTMENT, " +
						schema + TABLE_NAME2 + ".DIST_AMOUNT, " +
						schema + TABLE_NAME2 + ".BANK_ACCT_NBR, " +
						schema + TABLE_NAME2 + ".PARTIAL_ACH, " +
						schema + TABLE_NAME2 + ".ACH_BANK, " +
						schema + TABLE_NAME2 + ".ACCOUNT_TYPE, " +
						schema + TABLE_NAME2 + ".EBANK_ID, " +
						schema + TABLE_NAME2 + ".DIST_AMOUNT " +
			" FROM " + schema + TABLE_NAME + 
			" LEFT JOIN " + schema + TABLE_NAME2 +
			" ON " + TABLE_NAME + ".COMPANY = " + TABLE_NAME2 + ".COMPANY" +
			"    AND " + TABLE_NAME + ".CHECK_ID = " + TABLE_NAME2 + ".CHECK_ID" + 
			" " + (whereClause==null?"":whereClause);

			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next()){
				hasMoreRecs = true;
				pym = (TblPayMastr) createObject(rslt, new TblPayMastr());
//				pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
			}
			else
				hasMoreRecs = false;
			return pym;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

  public TblPayMastr findPayMastr(String employeeNumber) {
	try {
		
		if (whereClause==null) {
			whereClause = " WHERE EMPLOYEE=" + employeeNumber + " AND CHECK_ID=" + checkId;
		} else {
			whereClause = whereClause.trim() + " AND EMPLOYEE=" + employeeNumber + " AND CHECK_ID=" + checkId;
		}
//		sqlData = "SELECT * FROM " + data.getSchema() + TABLE_NAME + " " + 
//				(whereClause==null?"":whereClause);
		sqlData = "SELECT DISTINCT " + schema + TABLE_NAME + ".CHECK_ID, " +
				schema + TABLE_NAME + ".CHECK_DATE, " +
				schema + TABLE_NAME + ".CHECK_NBR, " +
				schema + TABLE_NAME + ".CHECK_NET, " +
				schema + TABLE_NAME + ".EMPLOYEE, " +
				schema + TABLE_NAME + ".PROCESS_LEVEL, " +
				schema + TABLE_NAME + ".DEPARTMENT, " +
				schema + TABLE_NAME2 + ".DIST_AMOUNT, " +
				schema + TABLE_NAME2 + ".BANK_ACCT_NBR, " +
				schema + TABLE_NAME2 + ".PARTIAL_ACH, " +
				schema + TABLE_NAME2 + ".ACH_BANK, " +
				schema + TABLE_NAME2 + ".ACCOUNT_TYPE, " +
				schema + TABLE_NAME2 + ".EBANK_ID, " +
				schema + TABLE_NAME2 + ".DIST_AMOUNT " +
		" FROM " + schema + TABLE_NAME + 
		" LEFT JOIN " + schema + TABLE_NAME2 +
		" ON " + TABLE_NAME + ".COMPANY = " + TABLE_NAME2 + ".COMPANY" +
		"    AND " + TABLE_NAME + ".CHECK_ID = " + TABLE_NAME2 + ".CHECK_ID" + 
		" " + (whereClause==null?"":whereClause);

		rslt = data.getDataStatement().executeQuery(sqlData);
		rsmd = rslt.getMetaData();
		rslt.next();
		pym = (TblPayMastr) createObject(rslt, new TblPayMastr());
//		pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
		return pym;
	} catch (SQLException e) {
	    // Could not connect to the database
		System.out.println("SQLException: " + e);
	} catch (InvocationTargetException ite) {
		System.out.println("InvocationException: " + ite);
	} catch (IllegalAccessException iae) {
		System.out.println("IllegalAccessException: " + iae);
	}
	return new TblPayMastr();
  }

  public TblPayMastr nextPayMastr() {
		try {
			if (rslt.next()){
				hasMoreRecs = true;
				pym = (TblPayMastr) createObject(rslt, new TblPayMastr());
//				pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
			}
			else
				hasMoreRecs = false;
			return pym;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

	public boolean hasMore () {
	  return hasMoreRecs;
	}

  public int countPayMastr() {
	try {
		countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) " +
				"FROM " + data.getSchema() + TABLE_NAME + " " + 
				(whereClause==null?"":whereClause));
		countRecs.next();
		return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
  }
  
  public void run() {
	  try {
		pym = findPayMastr();
//		errReport.initCounter("PYM Records " + this.whereClause.trim(), countPayMastr());
		while (hasMore()) {
			if(pym.getDist_amount()!=0) {
//				output.write(writePR560Layout(pym) + "\n");
				writePR560Layout(pym);
			} else {
				errReport.addToCounter("PYM Records Skipped", 1);
			}
			pym = nextPayMastr();
		}
	} catch (IOException e) {
		// Could not write properties file
		// Could not create output file
		System.out.println("IOException: " + e);
	}

  }
  
	//Output the PR560 format - Record Type 3
	public void writePR560Layout(TblPayMastr pym) throws IOException {

		String writeValue = null;

		writeValue =
				"3" + "," +						//RecType
				ClassMain.COMPANY + "," +
				PR560RecType1.getXrefEmployees().xlate(String.valueOf(pym.getEmployee()))	       + "," +	//Employee
//				pym.getEmployee() + "," +																	//Employee
				"\"" + PR560RecType1.getXrefProcessLevels().xlate(pym.getProcess_level() + pym.getDepartment()) + "\"" + "," +	//Process_level
//				pym.getProcess_level() + "," +												//Process_level
//				xDepartments.xlate(pym.getDepartment()) + "," +		//Department
//				pym.getDepartment() + "," +							//Department
				"," +												//Department - blank
				pym.getCheck_id() + "," +
				"," +
				"," +
				pym.getCheck_date() + "," +		//Tr_date
				pym.getCheck_date() + "," +		//Check_date
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				pym.getCheck_nbr() + "," +
				"," +
				"," +
				"," +
				pym.getCheck_net() + "," +
				pym.getDist_amount() + "," +
				pym.getBank_acct_nbr() + "," +
				pym.getPartial_ach() + "," +
				pym.getAch_bank() + "," +
				pym.getAccount_type() + "," +
				pym.getEbank_id() + "," +
				(pym.getDist_amount()==0.0?"N":"Y") + "," +			//Partial_ACH - Y/N based on distribution amount
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				","
			;
		
		//Check for Cross Reference Errors
		if (CrossRef.isError()) {
			//Increment Record in Error
			errReport.addToCounter("PYM Records in Error", 1);
			//Write to Error File
			errOutput.append(writeValue + "\n");
			errOutput.flush();
			CrossRef.resetError();
		}
		else {
			//Increment Record Written
			errReport.addToCounter("PYM Records Written", 1);
			//Write to Load File
			this.output.append(writeValue + "\n");
			this.output.flush();
		}
	}
}